use log::debug;
use rusqlite::{params, ToSql};
use tauri::State;

use crate::error::error::MyError;
use crate::model::db_connection::DatabaseConnection;
use crate::model::order::*;
use crate::model::output::Output;
use crate::model::pagination::Pagination;

#[tauri::command(rename_all = "snake_case")]
pub fn create_order(
    data: &str,
    state: State<DatabaseConnection>,
) -> Result<Output<Order>, MyError> {
    debug!("the data {:?}", data);

    // 首先尝试解析 JSON
    let order: Order = serde_json::from_str(data).map_err(|e| {
        MyError::new(
            1000,
            "Parse JSON error when create order".to_string(),
            e.to_string(),
        )
    })?;

    let conn = state.conn.lock().unwrap();

    // 修复 SQL 语句 - 参数数量应该匹配
    conn.execute(
        r#"INSERT INTO orders (
            material_id, client_id, contact_id, quantity, price, amount, unit,
            place_order_date, plan_delivery_date, actual_delivery_date,
            status, remark, image_path, created_by, modified_by
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"#,
        params![
            order.material_id,
            order.client_id,
            order.contact_id,
            order.quantity,
            order.price,
            order.amount, // 添加了 amount 字段
            order.unit,
            order.place_order_date,
            order.plan_delivery_date,
            order.actual_delivery_date,
            order.status,
            order.remark,
            order.image_path,
            "system".to_string(),
            "system".to_string()
        ],
    )
    .map_err(|e| MyError::new(1002, "Insert order error".to_string(), e.to_string()))?;

    // 获取最后插入的 ID
    let last_id = conn.last_insert_rowid();

    // 查询新创建的订单
    let mut stmt = conn.prepare("SELECT * FROM orders WHERE id = ?")?;
    let mut rows = stmt.query(params![last_id])?;

    if let Some(row) = rows.next()? {
        let order = Order {
            id: row.get(0)?,
            material_id: row.get(1)?,
            client_id: row.get(2)?,
            contact_id: row.get(3)?,
            quantity: row.get(4)?,
            price: row.get(5)?,
            amount: row.get(6)?,
            unit: row.get(7)?,
            place_order_date: row.get(8)?,
            plan_delivery_date: row.get(9)?,
            actual_delivery_date: row.get(10)?,
            status: row.get(11)?,
            remark: row.get(12)?,
            image_path: row.get(13)?,
            created_at: row.get(14)?,
            created_by: row.get(15)?,
            modified_at: row.get(16)?,
            modified_by: row.get(17)?,
            customer_name: None,
            customer_code: None,
            material_name: None,
            material_code: None,
            contact_name: None,
        };

        Ok(Output::success(
            "Order created successfully".to_string(),
            Some(order),
        ))
    } else {
        Err(MyError::new(
            1003,
            "Failed to retrieve created order".to_string(),
            "".to_string(),
        ))
    }
}

#[tauri::command(rename_all = "snake_case")]
pub fn update_order(
    data: &str,
    state: State<DatabaseConnection>,
) -> Result<Output<Order>, MyError> {
    let order: Order = serde_json::from_str(data).map_err(|e| {
        MyError::new(
            1000,
            "Parse JSON error when update order".to_string(),
            e.to_string(),
        )
    })?;

    let sql = r#"
        UPDATE orders
        SET
            material_id = ?1,
            client_id = ?2,
            contact_id = ?3,
            quantity = ?4,
            price = ?5,
            unit = ?6,
            place_order_date = ?7,
            plan_delivery_date = ?8,
            actual_delivery_date = ?9,
            status = ?10,
            remark = ?11,
            image_path = ?12

        WHERE id = ?13
    "#;

    let conn = state.conn.lock().unwrap();
    let affected_rows = conn
        .execute(
            sql,
            params![
                order.material_id,
                order.client_id,
                order.contact_id,
                order.quantity,
                order.price,
                order.unit,
                order.place_order_date,
                order.plan_delivery_date,
                order.actual_delivery_date,
                order.status,
                order.remark,
                order.image_path,
                order.id
            ],
        )
        .map_err(|e| MyError::database_error("Update order failed".to_string(), e.to_string()))?;

    if affected_rows == 0 {
        return Err(MyError::new(
            404,
            "order not found".to_string(),
            "".to_string(),
        ));
    }

    // 返回更新后的数据（可选）
    Ok(Output::success(
        "Update successful".to_string(),
        Some(order),
    ))
}

#[tauri::command(rename_all = "snake_case")]
pub fn list_order(
    customer_name: Option<String>,
    customer_code: Option<String>,
    material_code: Option<String>,
    contact: Option<String>,
    created_date: Option<String>,
    order_status: Option<i32>,
    page_current: Option<i32>, // 当前页码，从1开始
    page_size: Option<i32>,    // 每页大小
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Pagination<Order>>, MyError> {
    // 设置分页参数默认值
    let page_current = page_current.unwrap_or(1).max(1); // 至少为1
    let page_size = page_size.unwrap_or(20).max(1).min(100); // 限制在1-100之间

    // 构建基础SQL查询（用于计数）
    let mut count_sql = r#"SELECT COUNT(*)
    FROM orders o
    LEFT JOIN company c ON o.client_id = c.id
    LEFT JOIN material m ON o.material_id = m.id
    LEFT JOIN contact ct ON o.contact_id = ct.id
    WHERE 1=1"#
        .to_string();

    // 构建数据SQL查询
    let mut data_sql = r#"SELECT
        o.id, o.material_id, o.client_id, o.contact_id, o.quantity, o.price, o.amount, o.unit,
        o.place_order_date, o.plan_delivery_date, o.actual_delivery_date,
        o.status, o.remark, o.image_path, o.created_by, o.created_at, o.modified_by, o.modified_at,
        c.name as customer_name,
        m.name as material_name, m.code as material_code,
        ct.name as contact_name
    FROM orders o
    LEFT JOIN company c ON o.client_id = c.id
    LEFT JOIN material m ON o.material_id = m.id
    LEFT JOIN contact ct ON o.contact_id = ct.id
    WHERE 1=1"#
        .to_string();

    // 使用局部变量存储参数值
    let customer_name_param = customer_name.as_ref().map(|name| format!("%{}%", name));
    let customer_code_param = customer_code.as_ref().map(|code| format!("%{}%", code));
    let material_code_param = material_code.as_ref().map(|code| format!("%{}%", code));
    let contact_param = contact.as_ref().map(|name| format!("%{}%", name));

    let mut params: Vec<&dyn ToSql> = Vec::new();

    // 添加筛选条件（同时应用到计数和数据查询）
    if let Some(ref name) = customer_name_param {
        count_sql.push_str(" AND c.name LIKE ?");
        data_sql.push_str(" AND c.name LIKE ?");
        params.push(name);
    }
    if let Some(ref code) = customer_code_param {
        count_sql.push_str(" AND c.code LIKE ?");
        data_sql.push_str(" AND c.code LIKE ?");
        params.push(code);
    }
    if let Some(ref material_code) = material_code_param {
        count_sql.push_str(" AND m.code LIKE ?");
        data_sql.push_str(" AND m.code LIKE ?");
        params.push(material_code);
    }
    if let Some(ref contact_name) = contact_param {
        count_sql.push_str(" AND ct.name LIKE ?");
        data_sql.push_str(" AND ct.name LIKE ?");
        params.push(contact_name);
    }
    if let Some(ref date) = created_date {
        if !date.is_empty() {
            count_sql.push_str(" AND DATE(o.created_at) = ?");
            data_sql.push_str(" AND DATE(o.created_at) = ?");
            params.push(date);
        }
    }
    if let Some(ref status) = order_status {
        count_sql.push_str(" AND o.status = ?");
        data_sql.push_str(" AND o.status = ?");
        params.push(status);
    }

    // 添加排序和分页到数据查询
    data_sql.push_str(" ORDER BY o.created_at DESC");
    data_sql.push_str(" LIMIT ? OFFSET ?");

    let conn = state.conn.lock().unwrap();

    // 第一步：获取总记录数
    let total_count: i32 = {
        let mut stmt = conn.prepare(&count_sql).map_err(|e| {
            MyError::database_error(
                "Prepare count statement error in list_order".to_string(),
                e.to_string(),
            )
        })?;

        // 计算查询参数（只包含条件参数，不包含分页参数）
        let count_params: Vec<&dyn ToSql> = params.iter().map(|p| *p).collect();

        stmt.query_row(&*count_params, |row| row.get(0))
            .map_err(|e| {
                MyError::database_error(
                    "Query count error in list_order".to_string(),
                    e.to_string(),
                )
            })?
    };

    // 计算总页数
    let page_total = (total_count as f64 / page_size as f64).ceil() as i32;

    // 第二步：获取分页数据
    let mut data_params = params.clone();
    data_params.push(&page_size);
    let binding = (page_current - 1) * page_size;
    data_params.push(&binding);

    let mut stmt = conn.prepare(&data_sql).map_err(|e| {
        MyError::database_error(
            "Prepare data statement error in list_order".to_string(),
            e.to_string(),
        )
    })?;

    let rows = stmt
        .query_map(&*data_params, |row| {
            Ok(Order {
                id: row.get(0)?,
                material_id: row.get(1)?,
                client_id: row.get(2)?,
                contact_id: row.get(3)?,
                quantity: row.get(4)?,
                price: row.get(5)?,
                amount: row.get(6)?,
                unit: row.get(7)?,
                place_order_date: row.get(8)?,
                plan_delivery_date: row.get(9)?,
                actual_delivery_date: row.get(10)?,
                status: row.get(11)?,
                remark: row.get(12)?,
                image_path: row.get(13)?,
                created_by: row.get(14)?,
                created_at: row.get(15)?,
                modified_by: row.get(16)?,
                modified_at: row.get(17)?,
                // 扩展字段，用于前端显示
                customer_name: row.get(18)?,
                customer_code: None,
                material_name: row.get(19)?,
                material_code: row.get(20)?,
                contact_name: row.get(21)?,
            })
        })
        .map_err(|e| {
            MyError::database_error(
                "Query mapping error in list_order".to_string(),
                e.to_string(),
            )
        })?;

    let mut order_list = Vec::new();
    for row in rows {
        let order = row.map_err(|e| {
            MyError::database_error("Row mapping error in list_order".to_string(), e.to_string())
        })?;
        order_list.push(order);
    }

    // 构建分页响应
    let pagination = Pagination {
        page_size,
        page_total,
        page_current,
        page_items: order_list,
    };

    Ok(Output::success("OK".to_string(), Some(pagination)))
}

#[tauri::command(rename_all = "snake_case")]
pub fn order_detail(
    order_id: i32,
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Order>, MyError> {
    let conn = state.conn.lock().unwrap();

    let sql = "SELECT o.id,o.material_id, o.client_id, o.contact_id, o.quantity, o.price, o.amount, o.unit,
        o.place_order_date, o.plan_delivery_date, o.actual_delivery_date,
        o.status, o.remark, o.image_path, o.created_by, o.created_at, o.modified_by, o.modified_at, c.name as customer_name ,
        m.name as material_name, m.code as material_code,ct.name as contact_name
       FROM orders o left join company c on o.client_id = c.id
       left join contact ct on o.contact_id = ct.id
       left join material m on o.material_id = m.id
       WHERE o.id = ?";

    let mut stmt = conn.prepare(sql).map_err(|e| {
        MyError::database_error(
            "Prepare statement error in order_detail".to_string(),
            e.to_string(),
        )
    })?;

    let row = stmt
        .query_row(params![order_id], |row| {
            Ok(Order {
                id: row.get(0)?,
                material_id: row.get(1)?,
                client_id: row.get(2)?,
                contact_id: row.get(3)?,
                quantity: row.get(4)?,
                price: row.get(5)?,
                amount: row.get(6)?,
                unit: row.get(7)?,
                place_order_date: row.get(8)?,
                plan_delivery_date: row.get(9)?,
                actual_delivery_date: row.get(10)?,
                status: row.get(11)?,
                remark: row.get(12)?,
                image_path: row.get(13)?,
                created_by: row.get(14)?,
                created_at: row.get(15)?,
                modified_by: row.get(16)?,
                modified_at: row.get(17)?,
                // 扩展字段，用于前端显示
                customer_name: row.get(18)?,
                customer_code: None,
                material_name: row.get(19)?,
                material_code: row.get(20)?,
                contact_name: row.get(21)?,
            })
        })
        .map_err(|e| {
            MyError::database_error("Query row error in order_detail".to_string(), e.to_string())
        })?;

    //todo!("query contacts ");

    Ok(Output::success("OK".to_string(), Some(row)))
}
